package com.kcs.fxmargin.service;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.kcs.common.utils.ConnectionUtils;
import com.kcs.common.utils.Constant;
import com.kcs.common.utils.OIDGenerator;
import com.kcs.fxmargin.bo.FxMarginPreOrderBO;
import com.kcs.fxmargin.dao.PreOrderDao;
import com.kcs.fxmargin.model.TPreOrder;

public class PreOrderService {
	
	PreOrderDao dao = new PreOrderDao();
	
	public List<FxMarginPreOrderBO> searchPreOrder( String cifNo) throws Exception{
		Connection conn 						= null;
		PreparedStatement pstm 					= null;
		ResultSet rs							= null;
		List<FxMarginPreOrderBO> preOrderList 	= new ArrayList<FxMarginPreOrderBO>();
		
		try{
			conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO); // get connection fxmargin from mssql
			
			preOrderList = dao.searchPreOrder(conn, pstm, rs, cifNo);
			
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			if(conn != null ) conn.close();
			if(pstm != null ) pstm.close();
			if(rs   != null ) rs.close();
		}
		
		return preOrderList;
	}
	
	public String insert(TPreOrder preOrder) throws Exception {
		Connection conn 		= null;
		PreparedStatement pstm 	= null;
		CallableStatement cstm	= null;
		ResultSet rs			= null;
		String result			= "";
		
		try{
			conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO); // get connection fxmargin from mssql
			
			preOrder.setId(OIDGenerator.create());
						
			preOrder.setOrderNo(dao.genOrderNumber(conn, pstm, rs, preOrder));
			
			result = dao.insert(conn, pstm, preOrder);
			
			dao.callSPPreOrderCalMarginLimit(conn, cstm, preOrder.getCifNo());
			
			conn.commit();
		} catch (Exception e){
			result = "e";
			e.printStackTrace();
			conn.rollback();
		} finally {
			if(conn != null ) conn.close();
			if(pstm != null ) pstm.close();
		}
		
		return result;
	}
	
	public String update(TPreOrder preOrder) throws Exception {
		Connection conn 		= null;
		PreparedStatement pstm 	= null;
		CallableStatement cstm	= null;
		String result			= "";
		
		try{
			conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO); // get connection fxmargin from mssql
						
			result = dao.update(conn, pstm, preOrder);
			
			dao.callSPPreOrderCalMarginLimit(conn, cstm, preOrder.getCifNo());
			
			conn.commit();
		} catch (Exception e){
			result = "e";
			e.printStackTrace();
			conn.rollback();
		} finally {
			if(conn != null ) conn.close();
			if(pstm != null ) pstm.close();
		}
		
		return result;
	}
	
	public String delete(String id) throws Exception {
		Connection conn 		= null;
		PreparedStatement pstm 	= null;
		String result			= "";
		
		try{
			conn = ConnectionUtils.getConnection(Constant.MSSQL, Constant.TO); // get connection fxmargin from mssql
						
			result = dao.delete(conn, pstm, id);
			conn.commit();
		} catch (Exception e){
			result = "e";
			e.printStackTrace();
			conn.rollback();
		} finally {
			if(conn != null ) conn.close();
			if(pstm != null ) pstm.close();
		}
		
		return result;
	}
}
